********************************************************************************
** 	TITLE:		DE09_results                                                  ** 	
**	AUTHOR:	    Philippe Mongrain                                             **
**	DATA:       btw09_kerg                                                    **
**	VERSION:	Stata 16					                                  **
**	DATE:		October 2022  				                                  **
********************************************************************************

* Version control

version 16.0

* For incumbent reelection

do DE05_09_results

* 1. Download the "btw09_kerg.csv" file from this link: https://www.bundeswahlleiter.de/en/dam/jcr/0d80891b-aa0b-443b-9a45-007d28a9b817/btw09_kerg.csv
* 2. Save the "btw09_kerg.csv" file to the Excel format (i.e., "btw09_kerg.xlsx"")
* 3. Verify that the name of the Excel sheet is "btw09_kerg"

* Import the dataset

import excel "btw09_kerg.xlsx", sheet("btw09_kerg") cellrange(A9:EE340) clear

* Rename variables

rename A districtnumber09
rename B districtname09
rename C state09
rename D v09_1_Wahlberechtigte_1_End
rename E v05_1_Wahlberechtigte_1_Vor
rename F v09_2_Wahlberechtigte_2_End
rename G v05_2_Wahlberechtigte_2_Vor
rename H v09_1_Wähler_1_End
rename I v05_1_Wähler_1_Vor
rename J v09_2_Wähler_2_End
rename K v05_2_Wähler_2_Vor
rename L v09_1_Ungültige_1_End
rename M v05_1_Ungültige_1_Vor
rename N v09_2_Ungültige_2_End
rename O v05_2_Ungültige_2_Vor
rename P v09_1_Gültige_1_End
rename Q v05_1_Gültige_1_Vor
rename R v09_2_Gültige_2_End
rename S v05_2_Gültige_2_Vor
rename T v09_1_SPD_1_End
rename U v05_1_SPD_1_Vor
rename V v09_2_SPD_2_End
rename W v05_2_SPD_2_Vor
rename X v09_1_CDU_1_End
rename Y v05_1_CDU_1_Vor
rename Z v09_2_CDU_2_End
rename AA v05_2_CDU_2_Vor
rename AB v09_1_FDP_1_End
rename AC v05_1_FDP_1_Vor
rename AD v09_2_FDP_2_End
rename AE v05_2_FDP_2_Vor
rename AF v09_1_DIELINKE_1_End
rename AG v05_1_DIELINKE_1_Vor
rename AH v09_2_DIELINKE_2_End
rename AI v05_2_DIELINKE_2_Vor
rename AJ v09_1_GRÜNE_1_End
rename AK v05_1_GRÜNE_1_Vor
rename AL v09_2_GRÜNE_2_End
rename AM v05_2_GRÜNE_2_Vor
rename AN v09_1_CSU_1_End
rename AO v05_1_CSU_1_Vor
rename AP v09_2_CSU_2_End
rename AQ v05_2_CSU_2_Vor
rename AR v09_1_NPD_1_End
rename AS v05_1_NPD_1_Vor
rename AT v09_2_NPD_2_End
rename AU v05_2_NPD_2_Vor
rename AV v09_1_REP_1_End
rename AW v05_1_REP_1_Vor
rename AX v09_2_REP_2_End
rename AY v05_2_REP_2_Vor
rename AZ v09_1_FAMILIE_1_End
rename BA v05_1_FAMILIE_1_Vor
rename BB v09_2_FAMILIE_2_End
rename BC v05_2_FAMILIE_2_Vor
rename BD v09_1_Tierschutzpartei_1_End
rename BE v05_1_Tierschutzpartei_1_Vor
rename BF v09_2_Tierschutzpartei_2_End
rename BG v05_2_Tierschutzpartei_2_Vor
rename BH v09_1_PBC_1_End
rename BI v05_1_PBC_1_Vor
rename BJ v09_2_PBC_2_End
rename BK v05_2_PBC_2_Vor
rename BL v09_1_MLPD_1_End
rename BM v05_1_MLPD_1_Vor
rename BN v09_2_MLPD_2_End
rename BO v05_2_MLPD_2_Vor
rename BP v09_1_BüSo_1_End
rename BQ v05_1_BüSo_1_Vor
rename BR v09_2_BüSo_2_End
rename BS v05_2_BüSo_2_Vor
rename BT v09_1_BP_1_End
rename BU v05_1_BP_1_Vor
rename BV v09_2_BP_2_End
rename BW v05_2_BP_2_Vor
rename BX v09_1_PSG_1_End
rename BY v05_1_PSG_1_Vor
rename BZ v09_2_PSG_2_End
rename CA v05_2_PSG_2_Vor
rename CB v09_1_Volksabstimmung_1_End
rename CC v05_1_Volksabstimmung_1_Vor
rename CD v09_2_Volksabstimmung_2_End
rename CE v05_2_Volksabstimmung_2_Vor
rename CF v09_1_ZENTRUM_1_End
rename CG v05_1_ZENTRUM_1_Vor
rename CH v09_2_ZENTRUM_2_End
rename CI v05_2_ZENTRUM_2_Vor
rename CJ v09_1_ADM_1_End
rename CK v05_1_ADM_1_Vor
rename CL v09_2_ADM_2_End
rename CM v05_2_ADM_2_Vor
rename CN v09_1_CM_1_End
rename CO v05_1_CM_1_Vor
rename CP v09_2_CM_2_End
rename CQ v05_2_CM_2_Vor
rename CR v09_1_DKP_1_End
rename CS v05_1_DKP_1_Vor
rename CT v09_2_DKP_2_End
rename CU v05_2_DKP_2_Vor
rename CV v09_1_DVU_1_End
rename CW v05_1_DVU_1_Vor
rename CX v09_2_DVU_2_End
rename CY v05_2_DVU_2_Vor
rename CZ v09_1_DIEVIOLETTEN_1_End
rename DA v05_1_DIEVIOLETTEN_1_Vor
rename DB v09_2_DIEVIOLETTEN_2_End
rename DC v05_2_DIEVIOLETTEN_2_Vor
rename DD v09_1_FWD_1_End
rename DE v05_1_FWD_1_Vor
rename DF v09_2_FWD_2_End
rename DG v05_2_FWD_2_Vor
rename DH v09_1_ÖDP_1_End
rename DI v05_1_ÖDP_1_Vor
rename DJ v09_2_ÖDP_2_End
rename DK v05_2_ÖDP_2_Vor
rename DL v09_1_PIRATEN_1_End
rename DM v05_1_PIRATEN_1_Vor
rename DN v09_2_PIRATEN_2_End
rename DO v05_2_PIRATEN_2_Vor
rename DP v09_1_RRP_1_End
rename DQ v05_1_RRP_1_Vor
rename DR v09_2_RRP_2_End
rename DS v05_2_RRP_2_Vor
rename DT v09_1_RENTNER_1_End
rename DU v05_1_RENTNER_1_Vor
rename DV v09_2_RENTNER_2_End
rename DW v05_2_RENTNER_2_Vor
rename DX v09_1_FreieUnion_1_End
rename DY v05_1_FreieUnion_1_Vor
rename DZ v09_2_FreieUnion_2_End
rename EA v05_2_FreieUnion_2_Vor
rename EB v09_1_Übrige_1_End
rename EC v05_1_Übrige_1_Vor
rename ED v09_2_Übrige_2_End
rename EE v05_2_Übrige_2_Vor

* Reshape the dataset

drop if districtnumber09 == .
drop if districtname09 == "Bundesgebiet"
drop districtname09
drop if state09 == 99
drop state09

reshape long v09_1_ v05_1_ v09_2_ v05_2_, i(districtnumber09) j(var) string

* Drop second vote and 2005 results

drop if strpos(var, "_2_") > 0
drop if strpos(var, "_Vor") > 0
drop v09_2_ v05_1_ v05_2_

* Generate dichotomous variable for each party 

gen spd09 = strpos(var, "SPD") > 0
gen cdu09 = strpos(var, "CDU") > 0
gen fdp09 = strpos(var, "FDP") > 0
gen dielinke09 = strpos(var, "DIELINKE") > 0
gen grune09 = strpos(var, "GRÜNE") > 0
gen csu09 = strpos(var, "CSU") > 0
gen npd09 = strpos(var, "NPD") > 0
gen rep09 = strpos(var, "REP") > 0
gen familie09 = strpos(var, "FAMILIE") > 0
gen tier09 = strpos(var, "Tierschutzpartei") > 0
gen pbc09 = strpos(var, "PBC") > 0
gen mlpd09 = strpos(var, "MLPD") > 0
gen buso09 = strpos(var, "BüSo") > 0
gen bp09 = strpos(var, "BP") > 0
gen psg09 = strpos(var, "PSG") > 0
gen volks09 = strpos(var, "Volksabstimmung") > 0
gen zentrum09 = strpos(var, "ZENTRUM") > 0
gen adm09 = strpos(var, "ADM") > 0
gen cm09 = strpos(var, "CM") > 0
gen dkp09 = strpos(var, "DKP") > 0
gen dvu09 = strpos(var, "DVU") > 0
gen violetten09 = strpos(var, "DIEVIOLETTEN") > 0
gen odp09 = strpos(var, "ÖDP") > 0
gen fwd09 = strpos(var, "FWD") > 0
gen piraten09 = strpos(var, "PIRATEN") > 0
gen rrp09 = strpos(var, "RRP") > 0
gen rentner09 = strpos(var, "RENTNER") > 0
gen freieunion09 = strpos(var, "FreieUnion") > 0
gen other09 = strpos(var, "Übrige") > 0

* Generate party variable

gen party09 = 1 if cdu09 == 1
replace party09 = 4 if spd09 == 1
replace party09 = 5 if fdp09 == 1
replace party09 = 7 if dielinke09 == 1
replace party09 = 6 if grune09 == 1
replace party09 = 1 if csu09 == 1
replace party09 = 88 if party09 == . 

* Generate district names

gen districtname09 = ""

replace districtname09 = "Flensburg - Schleswig" if districtnumber09 == 1
replace districtname09 = "Nordfriesland - Dithmarschen Nord" if districtnumber09 == 2
replace districtname09 = "Steinburg - Dithmarschen Süd" if districtnumber09 == 3
replace districtname09 = "Rendsburg-Eckernförde" if districtnumber09 == 4
replace districtname09 = "Kiel" if districtnumber09 == 5
replace districtname09 = "Plön - Neumünster" if districtnumber09 == 6
replace districtname09 = "Pinneberg" if districtnumber09 == 7
replace districtname09 = "Segeberg - Stormarn-Nord" if districtnumber09 == 8
replace districtname09 = "Ostholstein" if districtnumber09 == 9
replace districtname09 = "Herzogtum Lauenburg - Stormarn-Süd" if districtnumber09 == 10
replace districtname09 = "Lübeck" if districtnumber09 == 11
replace districtname09 = "Wismar - Nordwestmecklenburg - Parchim" if districtnumber09 == 12
replace districtname09 = "Schwerin - Ludwigslust" if districtnumber09 == 13
replace districtname09 = "Rostock" if districtnumber09 == 14
replace districtname09 = "Stralsund - Nordvorpommern - Rügen" if districtnumber09 == 15
replace districtname09 = "Greifswald - Demmin - Ostvorpommern" if districtnumber09 == 16
replace districtname09 = "Bad Doberan - Güstrow - Müritz" if districtnumber09 == 17
replace districtname09 = "Neubrandenburg - Mecklenburg-Strelitz - Uecker-Randow" if districtnumber09 == 18
replace districtname09 = "Hamburg-Mitte" if districtnumber09 == 19
replace districtname09 = "Hamburg-Altona" if districtnumber09 == 20
replace districtname09 = "Hamburg-Eimsbüttel" if districtnumber09 == 21
replace districtname09 = "Hamburg-Nord" if districtnumber09 == 22
replace districtname09 = "Hamburg-Wandsbek" if districtnumber09 == 23
replace districtname09 = "Hamburg-Bergedorf - Harburg" if districtnumber09 == 24
replace districtname09 = "Aurich - Emden" if districtnumber09 == 25
replace districtname09 = "Unterems" if districtnumber09 == 26
replace districtname09 = "Friesland - Wilhelmshaven" if districtnumber09 == 27
replace districtname09 = "Oldenburg - Ammerland" if districtnumber09 == 28
replace districtname09 = "Delmenhorst - Wesermarsch - Oldenburg-Land" if districtnumber09 == 29
replace districtname09 = "Cuxhaven - Stade II" if districtnumber09 == 30
replace districtname09 = "Stade I - Rotenburg II" if districtnumber09 == 31
replace districtname09 = "Mittelems" if districtnumber09 == 32
replace districtname09 = "Cloppenburg - Vechta" if districtnumber09 == 33
replace districtname09 = "Diepholz - Nienburg I" if districtnumber09 == 34
replace districtname09 = "Osterholz - Verden" if districtnumber09 == 35
replace districtname09 = "Rotenburg I - Soltau-Fallingbostel" if districtnumber09 == 36
replace districtname09 = "Harburg" if districtnumber09 == 37
replace districtname09 = "Lüchow-Dannenberg - Lüneburg" if districtnumber09 == 38
replace districtname09 = "Osnabrück-Land" if districtnumber09 == 39
replace districtname09 = "Stadt Osnabrück" if districtnumber09 == 40
replace districtname09 = "Nienburg II - Schaumburg" if districtnumber09 == 41
replace districtname09 = "Stadt Hannover I" if districtnumber09 == 42
replace districtname09 = "Stadt Hannover II" if districtnumber09 == 43
replace districtname09 = "Hannover-Land I" if districtnumber09 == 44
replace districtname09 = "Celle - Uelzen" if districtnumber09 == 45
replace districtname09 = "Gifhorn - Peine" if districtnumber09 == 46
replace districtname09 = "Hameln-Pyrmont - Holzminden" if districtnumber09 == 47
replace districtname09 = "Hannover-Land II" if districtnumber09 == 48
replace districtname09 = "Hildesheim" if districtnumber09 == 49
replace districtname09 = "Salzgitter - Wolfenbüttel" if districtnumber09 == 50
replace districtname09 = "Braunschweig" if districtnumber09 == 51
replace districtname09 = "Helmstedt - Wolfsburg" if districtnumber09 == 52
replace districtname09 = "Goslar - Northeim - Osterode" if districtnumber09 == 53
replace districtname09 = "Göttingen" if districtnumber09 == 54
replace districtname09 = "Bremen I" if districtnumber09 == 55
replace districtname09 = "Bremen II - Bremerhaven" if districtnumber09 == 56
replace districtname09 = "Prignitz - Ostprignitz-Ruppin - Havelland I" if districtnumber09 == 57
replace districtname09 = "Uckermark - Barnim I" if districtnumber09 == 58
replace districtname09 = "Oberhavel - Havelland II" if districtnumber09 == 59
replace districtname09 = "Märkisch-Oderland - Barnim II" if districtnumber09 == 60
replace districtname09 = "Brandenburg an der Havel - Potsdam-Mittelmark I - Havelland III - Teltow-Fläming I" if districtnumber09 == 61
replace districtname09 = "Potsdam - Potsdam-Mittelmark II - Teltow-Fläming II" if districtnumber09 == 62
replace districtname09 = "Dahme-Spreewald - Teltow-Fläming III - Oberspreewald-Lausitz I" if districtnumber09 == 63
replace districtname09 = "Frankfurt (Oder) - Oder-Spree" if districtnumber09 == 64
replace districtname09 = "Cottbus - Spree-Neiße" if districtnumber09 == 65
replace districtname09 = "Elbe-Elster - Oberspreewald-Lausitz II" if districtnumber09 == 66
replace districtname09 = "Altmark" if districtnumber09 == 67
replace districtname09 = "Börde - Jerichower Land" if districtnumber09 == 68
replace districtname09 = "Harz" if districtnumber09 == 69
replace districtname09 = "Magdeburg" if districtnumber09 == 70
replace districtname09 = "Dessau - Wittenberg" if districtnumber09 == 71
replace districtname09 = "Anhalt" if districtnumber09 == 72
replace districtname09 = "Halle" if districtnumber09 == 73
replace districtname09 = "Burgenland - Saalekreis" if districtnumber09 == 74
replace districtname09 = "Mansfeld" if districtnumber09 == 75
replace districtname09 = "Berlin-Mitte" if districtnumber09 == 76
replace districtname09 = "Berlin-Pankow" if districtnumber09 == 77
replace districtname09 = "Berlin-Reinickendorf" if districtnumber09 == 78
replace districtname09 = "Berlin-Spandau - Charlottenburg Nord" if districtnumber09 == 79
replace districtname09 = "Berlin-Steglitz - Zehlendorf" if districtnumber09 == 80
replace districtname09 = "Berlin-Charlottenburg - Wilmersdorf" if districtnumber09 == 81
replace districtname09 = "Berlin-Tempelhof - Schöneberg" if districtnumber09 == 82
replace districtname09 = "Berlin-Neukölln" if districtnumber09 == 83
replace districtname09 = "Berlin-Friedrichshain - Kreuzberg - Prenzlauer Berg Ost" if districtnumber09 == 84
replace districtname09 = "Berlin-Treptow - Köpenick" if districtnumber09 == 85
replace districtname09 = "Berlin-Marzahn - Hellersdorf" if districtnumber09 == 86
replace districtname09 = "Berlin-Lichtenberg" if districtnumber09 == 87
replace districtname09 = "Aachen" if districtnumber09 == 88
replace districtname09 = "Kreis Aachen" if districtnumber09 == 89
replace districtname09 = "Heinsberg" if districtnumber09 == 90
replace districtname09 = "Düren" if districtnumber09 == 91
replace districtname09 = "Erftkreis I" if districtnumber09 == 92
replace districtname09 = "Euskirchen - Erftkreis II" if districtnumber09 == 93
replace districtname09 = "Köln I" if districtnumber09 == 94
replace districtname09 = "Köln II" if districtnumber09 == 95
replace districtname09 = "Köln III" if districtnumber09 == 96
replace districtname09 = "Bonn" if districtnumber09 == 97
replace districtname09 = "Rhein-Sieg-Kreis I" if districtnumber09 == 98
replace districtname09 = "Rhein-Sieg-Kreis II" if districtnumber09 == 99
replace districtname09 = "Oberbergischer Kreis" if districtnumber09 == 100
replace districtname09 = "Rheinisch-Bergischer Kreis" if districtnumber09 == 101
replace districtname09 = "Leverkusen - Köln IV" if districtnumber09 == 102
replace districtname09 = "Wuppertal I" if districtnumber09 == 103
replace districtname09 = "Solingen - Remscheid - Wuppertal II" if districtnumber09 == 104
replace districtname09 = "Mettmann I" if districtnumber09 == 105
replace districtname09 = "Mettmann II" if districtnumber09 == 106
replace districtname09 = "Düsseldorf I" if districtnumber09 == 107
replace districtname09 = "Düsseldorf II" if districtnumber09 == 108
replace districtname09 = "Neuss I" if districtnumber09 == 109
replace districtname09 = "Mönchengladbach" if districtnumber09 == 110
replace districtname09 = "Krefeld I - Neuss II" if districtnumber09 == 111
replace districtname09 = "Viersen" if districtnumber09 == 112
replace districtname09 = "Kleve" if districtnumber09 == 113
replace districtname09 = "Wesel I" if districtnumber09 == 114
replace districtname09 = "Krefeld II - Wesel II" if districtnumber09 == 115
replace districtname09 = "Duisburg I" if districtnumber09 == 116
replace districtname09 = "Duisburg II" if districtnumber09 == 117
replace districtname09 = "Oberhausen - Wesel III" if districtnumber09 == 118
replace districtname09 = "Mülheim - Essen I" if districtnumber09 == 119
replace districtname09 = "Essen II" if districtnumber09 == 120
replace districtname09 = "Essen III" if districtnumber09 == 121
replace districtname09 = "Recklinghausen I" if districtnumber09 == 122
replace districtname09 = "Recklinghausen II" if districtnumber09 == 123
replace districtname09 = "Gelsenkirchen" if districtnumber09 == 124
replace districtname09 = "Steinfurt I - Borken I" if districtnumber09 == 125
replace districtname09 = "Bottrop - Recklinghausen III" if districtnumber09 == 126
replace districtname09 = "Borken II" if districtnumber09 == 127
replace districtname09 = "Coesfeld - Steinfurt II" if districtnumber09 == 128
replace districtname09 = "Steinfurt III" if districtnumber09 == 129
replace districtname09 = "Münster" if districtnumber09 == 130
replace districtname09 = "Warendorf" if districtnumber09 == 131
replace districtname09 = "Gütersloh" if districtnumber09 == 132
replace districtname09 = "Bielefeld" if districtnumber09 == 133
replace districtname09 = "Herford - Minden-Lübbecke II" if districtnumber09 == 134
replace districtname09 = "Minden-Lübbecke I" if districtnumber09 == 135
replace districtname09 = "Lippe I" if districtnumber09 == 136
replace districtname09 = "Höxter - Lippe II" if districtnumber09 == 137
replace districtname09 = "Paderborn" if districtnumber09 == 138
replace districtname09 = "Hagen - Ennepe-Ruhr-Kreis I" if districtnumber09 == 139
replace districtname09 = "Ennepe-Ruhr-Kreis II" if districtnumber09 == 140
replace districtname09 = "Bochum I" if districtnumber09 == 141
replace districtname09 = "Herne - Bochum II" if districtnumber09 == 142
replace districtname09 = "Dortmund I" if districtnumber09 == 143
replace districtname09 = "Dortmund II" if districtnumber09 == 144
replace districtname09 = "Unna I" if districtnumber09 == 145
replace districtname09 = "Hamm - Unna II" if districtnumber09 == 146
replace districtname09 = "Soest" if districtnumber09 == 147
replace districtname09 = "Hochsauerlandkreis" if districtnumber09 == 148
replace districtname09 = "Siegen-Wittgenstein" if districtnumber09 == 149
replace districtname09 = "Olpe - Märkischer Kreis I" if districtnumber09 == 150
replace districtname09 = "Märkischer Kreis II" if districtnumber09 == 151
replace districtname09 = "Nordsachsen" if districtnumber09 == 152
replace districtname09 = "Leipzig I" if districtnumber09 == 153
replace districtname09 = "Leipzig II" if districtnumber09 == 154
replace districtname09 = "Leipzig-Land" if districtnumber09 == 155
replace districtname09 = "Meißen" if districtnumber09 == 156
replace districtname09 = "Bautzen I" if districtnumber09 == 157
replace districtname09 = "Görlitz" if districtnumber09 == 158
replace districtname09 = "Sächsische Schweiz - Osterzgebirge" if districtnumber09 == 159
replace districtname09 = "Dresden I" if districtnumber09 == 160
replace districtname09 = "Dresden II - Bautzen II" if districtnumber09 == 161
replace districtname09 = "Mittelsachsen" if districtnumber09 == 162
replace districtname09 = "Chemnitz" if districtnumber09 == 163
replace districtname09 = "Chemnitzer Umland - Erzgebirgskreis II" if districtnumber09 == 164
replace districtname09 = "Erzgebirgskreis I" if districtnumber09 == 165
replace districtname09 = "Zwickau" if districtnumber09 == 166
replace districtname09 = "Vogtlandkreis" if districtnumber09 == 167
replace districtname09 = "Waldeck" if districtnumber09 == 168
replace districtname09 = "Kassel" if districtnumber09 == 169
replace districtname09 = "Werra-Meißner - Hersfeld-Rotenburg" if districtnumber09 == 170
replace districtname09 = "Schwalm-Eder" if districtnumber09 == 171
replace districtname09 = "Marburg" if districtnumber09 == 172
replace districtname09 = "Lahn-Dill" if districtnumber09 == 173
replace districtname09 = "Gießen" if districtnumber09 == 174
replace districtname09 = "Fulda" if districtnumber09 == 175
replace districtname09 = "Hochtaunus" if districtnumber09 == 176
replace districtname09 = "Wetterau" if districtnumber09 == 177
replace districtname09 = "Rheingau-Taunus - Limburg" if districtnumber09 == 178
replace districtname09 = "Wiesbaden" if districtnumber09 == 179
replace districtname09 = "Hanau" if districtnumber09 == 180
replace districtname09 = "Main-Taunus" if districtnumber09 == 181
replace districtname09 = "Frankfurt am Main I" if districtnumber09 == 182
replace districtname09 = "Frankfurt am Main II" if districtnumber09 == 183
replace districtname09 = "Groß-Gerau" if districtnumber09 == 184
replace districtname09 = "Offenbach" if districtnumber09 == 185
replace districtname09 = "Darmstadt" if districtnumber09 == 186
replace districtname09 = "Odenwald" if districtnumber09 == 187
replace districtname09 = "Bergstraße" if districtnumber09 == 188
replace districtname09 = "Eichsfeld - Nordhausen - Unstrut-Hainich-Kreis I" if districtnumber09 == 189
replace districtname09 = "Eisenach - Wartburgkreis - Unstrut-Hainich-Kreis II" if districtnumber09 == 190
replace districtname09 = "Kyffhäuserkreis - Sömmerda - Weimarer Land I" if districtnumber09 == 191
replace districtname09 = "Gotha - Ilm-Kreis" if districtnumber09 == 192
replace districtname09 = "Erfurt - Weimar - Weimarer Land II" if districtnumber09 == 193
replace districtname09 = "Gera - Jena - Saale-Holzland-Kreis" if districtnumber09 == 194
replace districtname09 = "Greiz - Altenburger Land" if districtnumber09 == 195
replace districtname09 = "Sonneberg - Saalfeld-Rudolstadt - Saale-Orla-Kreis" if districtnumber09 == 196
replace districtname09 = "Suhl - Schmalkalden-Meiningen - Hildburghausen" if districtnumber09 == 197
replace districtname09 = "Neuwied" if districtnumber09 == 198
replace districtname09 = "Ahrweiler" if districtnumber09 == 199
replace districtname09 = "Koblenz" if districtnumber09 == 200
replace districtname09 = "Mosel/Rhein-Hunsrück" if districtnumber09 == 201
replace districtname09 = "Kreuznach" if districtnumber09 == 202
replace districtname09 = "Bitburg" if districtnumber09 == 203
replace districtname09 = "Trier" if districtnumber09 == 204
replace districtname09 = "Montabaur" if districtnumber09 == 205
replace districtname09 = "Mainz" if districtnumber09 == 206
replace districtname09 = "Worms" if districtnumber09 == 207
replace districtname09 = "Ludwigshafen/Frankenthal" if districtnumber09 == 208
replace districtname09 = "Neustadt - Speyer" if districtnumber09 == 209
replace districtname09 = "Kaiserslautern" if districtnumber09 == 210
replace districtname09 = "Pirmasens" if districtnumber09 == 211
replace districtname09 = "Südpfalz" if districtnumber09 == 212
replace districtname09 = "Altötting" if districtnumber09 == 213
replace districtname09 = "Erding - Ebersberg" if districtnumber09 == 214
replace districtname09 = "Freising" if districtnumber09 == 215
replace districtname09 = "Fürstenfeldbruck" if districtnumber09 == 216
replace districtname09 = "Ingolstadt" if districtnumber09 == 217
replace districtname09 = "München-Nord" if districtnumber09 == 218
replace districtname09 = "München-Ost" if districtnumber09 == 219
replace districtname09 = "München-Süd" if districtnumber09 == 220
replace districtname09 = "München-West/Mitte" if districtnumber09 == 221
replace districtname09 = "München-Land" if districtnumber09 == 222
replace districtname09 = "Rosenheim" if districtnumber09 == 223
replace districtname09 = "Starnberg" if districtnumber09 == 224
replace districtname09 = "Traunstein" if districtnumber09 == 225
replace districtname09 = "Weilheim" if districtnumber09 == 226
replace districtname09 = "Deggendorf" if districtnumber09 == 227
replace districtname09 = "Landshut" if districtnumber09 == 228
replace districtname09 = "Passau" if districtnumber09 == 229
replace districtname09 = "Rottal-Inn" if districtnumber09 == 230
replace districtname09 = "Straubing" if districtnumber09 == 231
replace districtname09 = "Amberg" if districtnumber09 == 232
replace districtname09 = "Regensburg" if districtnumber09 == 233
replace districtname09 = "Schwandorf" if districtnumber09 == 234
replace districtname09 = "Weiden" if districtnumber09 == 235
replace districtname09 = "Bamberg" if districtnumber09 == 236
replace districtname09 = "Bayreuth" if districtnumber09 == 237
replace districtname09 = "Coburg" if districtnumber09 == 238
replace districtname09 = "Hof" if districtnumber09 == 239
replace districtname09 = "Kulmbach" if districtnumber09 == 240
replace districtname09 = "Ansbach" if districtnumber09 == 241
replace districtname09 = "Erlangen" if districtnumber09 == 242
replace districtname09 = "Fürth" if districtnumber09 == 243
replace districtname09 = "Nürnberg-Nord" if districtnumber09 == 244
replace districtname09 = "Nürnberg-Süd" if districtnumber09 == 245
replace districtname09 = "Roth" if districtnumber09 == 246
replace districtname09 = "Aschaffenburg" if districtnumber09 == 247
replace districtname09 = "Bad Kissingen" if districtnumber09 == 248
replace districtname09 = "Main-Spessart" if districtnumber09 == 249
replace districtname09 = "Schweinfurt" if districtnumber09 == 250
replace districtname09 = "Würzburg" if districtnumber09 == 251
replace districtname09 = "Augsburg-Stadt" if districtnumber09 == 252
replace districtname09 = "Augsburg-Land" if districtnumber09 == 253
replace districtname09 = "Donau-Ries" if districtnumber09 == 254
replace districtname09 = "Neu-Ulm" if districtnumber09 == 255
replace districtname09 = "Oberallgäu" if districtnumber09 == 256
replace districtname09 = "Ostallgäu" if districtnumber09 == 257
replace districtname09 = "Stuttgart I" if districtnumber09 == 258
replace districtname09 = "Stuttgart II" if districtnumber09 == 259
replace districtname09 = "Böblingen" if districtnumber09 == 260
replace districtname09 = "Esslingen" if districtnumber09 == 261
replace districtname09 = "Nürtingen" if districtnumber09 == 262
replace districtname09 = "Göppingen" if districtnumber09 == 263
replace districtname09 = "Waiblingen" if districtnumber09 == 264
replace districtname09 = "Ludwigsburg" if districtnumber09 == 265
replace districtname09 = "Neckar-Zaber" if districtnumber09 == 266
replace districtname09 = "Heilbronn" if districtnumber09 == 267
replace districtname09 = "Schwäbisch Hall - Hohenlohe" if districtnumber09 == 268
replace districtname09 = "Backnang - Schwäbisch Gmünd" if districtnumber09 == 269
replace districtname09 = "Aalen - Heidenheim" if districtnumber09 == 270
replace districtname09 = "Karlsruhe-Stadt" if districtnumber09 == 271
replace districtname09 = "Karlsruhe-Land" if districtnumber09 == 272
replace districtname09 = "Rastatt" if districtnumber09 == 273
replace districtname09 = "Heidelberg" if districtnumber09 == 274
replace districtname09 = "Mannheim" if districtnumber09 == 275
replace districtname09 = "Odenwald - Tauber" if districtnumber09 == 276
replace districtname09 = "Rhein-Neckar" if districtnumber09 == 277
replace districtname09 = "Bruchsal - Schwetzingen" if districtnumber09 == 278
replace districtname09 = "Pforzheim" if districtnumber09 == 279
replace districtname09 = "Calw" if districtnumber09 == 280
replace districtname09 = "Freiburg" if districtnumber09 == 281
replace districtname09 = "Lörrach - Müllheim" if districtnumber09 == 282
replace districtname09 = "Emmendingen - Lahr" if districtnumber09 == 283
replace districtname09 = "Offenburg" if districtnumber09 == 284
replace districtname09 = "Rottweil - Tuttlingen" if districtnumber09 == 285
replace districtname09 = "Schwarzwald-Baar" if districtnumber09 == 286
replace districtname09 = "Konstanz" if districtnumber09 == 287
replace districtname09 = "Waldshut" if districtnumber09 == 288
replace districtname09 = "Reutlingen" if districtnumber09 == 289
replace districtname09 = "Tübingen" if districtnumber09 == 290
replace districtname09 = "Ulm" if districtnumber09 == 291
replace districtname09 = "Biberach" if districtnumber09 == 292
replace districtname09 = "Bodensee" if districtnumber09 == 293
replace districtname09 = "Ravensburg" if districtnumber09 == 294
replace districtname09 = "Zollernalb - Sigmaringen" if districtnumber09 == 295
replace districtname09 = "Saarbrücken" if districtnumber09 == 296
replace districtname09 = "Saarlouis" if districtnumber09 == 297
replace districtname09 = "St. Wendel" if districtnumber09 == 298
replace districtname09 = "Homburg" if districtnumber09 == 299

* Generate vote percentages

rename v09_1_ numberofvotes09

gen valid09 = strpos(var, "Gültige") > 0

gen validvotes09 = numberofvotes09 if valid09 == 1

gsort districtname09 validvotes09
bysort districtname09 : gen numberofvalidvotes09 = validvotes09[1]

drop if strpos(var, "Wahlberechtigte_1_End") > 0
drop if strpos(var, "Wähler_1_End") > 0
drop if strpos(var, "Ungültige_1_End") > 0
drop if strpos(var, "Gültige_1_End") > 0

gsort districtname09 -numberofvotes09

bysort districtname09 : gen rank09 = _n

gen numberofvotespercent09 = (numberofvotes09/numberofvalidvotes09)*100

drop if numberofvotespercent09 == .

* Generate rank of parties

gen first09 = party09 if rank09 == 1
gen second09 = party09 if rank09 == 2
gen third09 = party09 if rank09 == 3

bysort districtname09 : gen winner09 = first09[1]
bysort districtname09 : gen runnerup09 = second09[2]
bysort districtname09 : gen thirdplace09 = third09[3]

* Generate margins of victory

bysort districtname09 : gen margin09 = numberofvotespercent09[1] - numberofvotespercent09[2]

* Generate effective number of electoral parties

gen sqrvotepr09 = (numberofvotespercent09/100)^2

bysort districtname09 : egen sumsqrvotepr09 = sum(sqrvotepr09)

gen enep09 = 1/sumsqrvotepr09

* Labelling parties

label define party 1 "CDU/CSU" 4 "SPD" 5 "FDP" 6 "Green" 7 "Die Linke" 88 "Other"
label values winner09 runnerup09 thirdplace09 party

* Selecting, ordering, and sorting variables

keep districtnumber09 districtname09 party09 rank09 numberofvotes09 numberofvotespercent09 margin09 enep09 winner09 runnerup09 thirdplace09

order districtnumber09 districtname09 party09 rank09 numberofvotes09 numberofvotespercent09 margin09 enep09 winner09 runnerup09 thirdplace09

sort districtnumber09 rank09

drop if rank09 > 3

* Drop duplicates

duplicates tag districtnumber09, gen(dup)
duplicates drop districtnumber09, force
drop dup

* Create election variable

gen election09 = "2009 German federal election"

* Saving file

save DE09_results, replace

* Merge with DE05_09_results.dta

merge 1:1 _n using DE05_09_results.dta

* Generate variable for incumbent party reelection

gen reelected09 = 1 if winner09 == winner05
replace reelected09 = 0 if reelected09!=1

* Saving file

save DE0509_results, replace